package xls;

import jxl.*;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.*;

import java.io.File;
import java.io.IOException;
import java.nio.channels.WritableByteChannel;
import java.util.Locale;

/**
 * User: Vladimir
 * Date: 01.07.12 Time: 16:01
 */
public class Blanker {
    public static void main(String[] args) throws BiffException, IOException, WriteException {
        File formFile = new File("excel/forma-113_0.xls");
        Workbook workbook = Workbook.getWorkbook(formFile);
        File tmpFile = new File("excel/temp.xls");
        WritableWorkbook writableTempWorkbook = Workbook.createWorkbook(tmpFile, workbook);
        WritableWorkbook writableWorkbook = createWorkbook("excel/forma-113_1.xls");
        WritableSheet sourceSheet = writableTempWorkbook.getSheet(0);
        WritableSheet targetSheet = writableWorkbook.getSheet(0);
        System.out.println(sourceSheet.getRows());
        System.out.println(sourceSheet.getColumns());
        Range[] mergedCells = sourceSheet.getMergedCells();
        for (Range mergedCell : mergedCells) {
            targetSheet.mergeCells(mergedCell.getTopLeft().getColumn(),
                    mergedCell.getTopLeft().getRow(),
                    mergedCell.getBottomRight().getColumn(),
                    mergedCell.getBottomRight().getRow());
        }
        for (int row = 0; row < sourceSheet.getRows(); row++) {
            targetSheet.setRowView(row, sourceSheet.getRowView(row));
            for (int col = 0; col < sourceSheet.getColumns(); col++) {
                WritableCell sourceCell = sourceSheet.getWritableCell(col, row);
                WritableCell targetCell = sourceCell.copyTo(col, row);
                CellFormat sourceFormat = sourceCell.getCellFormat();
                if (sourceFormat != null) {
                    WritableCellFormat targetFormat = new WritableCellFormat(sourceFormat);
                    targetCell.setCellFormat(targetFormat);
                }
                targetSheet.setColumnView(col, sourceSheet.getColumnView(col));
                targetSheet.addCell(targetCell);
//                if (sourceCell.getRow() == 92 && sourceCell.getColumn() == 5) {
//                    System.out.println();
//                }
                if (sourceCell.getContents().equals("От кого")) {
                    System.out.println();
                }
//                Cell cell = sourceSheet.getCell(col, row);
//                if (cell.getContents().startsWith("${")) {
//                    System.out.println(cell.getContents());
//                }
            }
        }
        workbookWriteAndClose(writableWorkbook);
        workbook.close();
        writableTempWorkbook.close();
        tmpFile.delete();
    }

    private static WritableWorkbook createWorkbook(String path) throws IOException {
        File xlsFile = new File(path);
        WorkbookSettings s = new WorkbookSettings();
        s.setLocale(new Locale("ru", "RU"));
        s.setEncoding("KOI8-R");
        WritableWorkbook workbook = Workbook.createWorkbook(xlsFile, s);
        workbook.createSheet("лицевая сторона", 0);
        workbook.createSheet("оборотная сторона", 0);
        return workbook;
    }

    private static void workbookWriteAndClose(WritableWorkbook workbook) throws IOException, WriteException {
        workbook.write();
        workbook.close();
    }
}
